package com.apes.fn.scm.retailOrder.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @program:
 * @description
 * @author: lws
 * @create: 2022-09-07 08:47:56
 **/
@Component
public class RetailOrderDao extends ApesDao {

    /**
     * 分页查询
     * @param param
     * @return
     */
    public JSONArray retailOrderByPage(JSONObject param) {

        StringBuffer sb = new StringBuffer();

        sb.append("SELECT * FROM (\n" +
                " SELECT rownum rn,\n" +
                "        A.ID,\n" +
                "        (SELECT E.car_license FROM fn_customer_expand E WHERE A.customer_expand_id = E.ID) car_license,\n" +
                "        (SELECT ('['|| E.ID || ']' || E.NAME) FROM mas_dept E WHERE E.ID = A.DEPT_ID) DEPT_NAME,\n" +
                "        (SELECT ('[' || E.ID || ']' || E.NAME) FROM mas_dept E WHERE E.ID = C.fixed_Loss_Dept) fixed_Loss_Dept,\n" +
                "        (SELECT E.NAME FROM mas_payment_method E WHERE E.ID = O.Payment_Method_Type) Payment_Method_Type,\n" +
                "        O.amount AMOUNT,\n" +
                "        (SELECT E.NAME FROM crm_insurance_company E WHERE E.ID = A.insurance_company_id) insurance_company_NAME,\n" +
                "        (SELECT E.NAME FROM soe_business_attr_value E WHERE E.ID = C.ORDER_TYPE) LOSS_TYPE,\n" +
                "        TO_CHAR(B.RECEIVABLE_DATE,'YYYY-MM-DD HH24:MI:SS') RECEIVABLE_DATE\n" +
                " FROM retail_order A\n" +
                " LEFT JOIN ser_service_plan B ON A.ORIGIN = B.ID\n" +
                " LEFT JOIN ser_loss_quotation C ON B.SOURCE_FROM = C.ID\n" +
                " LEFT JOIN Retail_Order_Payment_Method O ON A.ID = O.RETAIL_ORDER_ID \n" +
                " where A.claim_Type IS NOT NULL AND rownum <= :maxIndex");
        SQLCondition(sb,param);
        sb.append(") where rn >= :minIndex and rn <= :maxIndex");

        return toJSONArray(find(sb.toString(), param));

    }



    /**
     * 分页查询
     * @param param
     * @return
     */
    public JSONArray findDept(JSONObject param,String dept) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT a.id as id, a.order_type as ORDER_TYPE, a.origin as ORIGIN, a.company_id as COMPANY_ID,'零售' ORDERNAME," +
                "(SELECT C.NAME FROM MAS_PARTY_ROLE B, MAS_PARTY C WHERE A.COMPANY_ID = B.ID AND B.PARTY_ID = C.ID) COMPANY_NAME , a.actual_total ACTUALAMOUNT," +
                "a.invoiced_amount INVOICEDAMOUNT, to_char(a.date_approve,'yyyy-mm-dd') DATEAPPROVE, a.approve_uid APPROVEUID," +
                "b.realname REALNAME,b.car_license as CARLICENSE,a.dept_id  as DEPT_ID,(SELECT B.NAME FROM MAS_DEPT B WHERE A.DEPT_ID = B.ID) DEPT_NAME," +
                "(a.actual_total- a.invoiced_amount) as UNINVOICEDAMOUNT" +
                "  FROM retail_order a, fn_customer_expand b" +
                " where a.customer_expand_id = b.id" +
                "   and a.dept_id = '"+dept+"'" +
                "   and a.state = 'vouched'" +
                "   and a.order_type <> 'yearcard'" +
                "   and a.order_type <> 'storedValue'" +
                "   and a.is_return_all = 0" +
                "   and a.actual_total > a.invoiced_amount");
        SQLCondition(sb,param);
        if (param.get("dateApprove") != null) {
            sb.append(" AND a.date_approve >= to_date(substr(:dateApprove_DATE,1,10),'yyyy-MM-dd') AND a.date_approve < to_date(substr(:dateApprove,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("carInfo") != null) {
            sb.append(" AND (b.car_license = :carInfo or b.realname = :carInfo or b.mobile = :carInfo)");
        }
        if (param.get("orderInfo") != null) {
            if(param.get("orderInfo").toString().length() > 10){
                sb.append(" AND (a.id = :orderInfo or a.ORIGIN = :orderInfo )");
            }else{
                sb.append(" AND  a.actual_total = :orderInfo");
            }

        }
        sb.append(" order by a.date_approve desc");
        return toJSONArray(find(sb.toString(), param));

    }



    /**
     * 分页查询总数
     * @param param
     * @return
     */
    public JSONArray retailOrderByPageTotal(JSONObject param) {

        StringBuffer sb = new StringBuffer();

        sb.append(" SELECT count(*) total\n" +
                "   FROM retail_order A\n" +
                "   LEFT JOIN ser_service_plan B ON A.ORIGIN = B.ID\n" +
                "   LEFT JOIN ser_loss_quotation C ON B.SOURCE_FROM = C.ID\n" +
                "   LEFT JOIN Retail_Order_Payment_Method O ON A.ID = O.RETAIL_ORDER_ID \n" +
                "   where A.claim_Type IS NOT NULL ");
        SQLCondition(sb,param);

        return toJSONArray(find(sb.toString(), param));

    }

    public JSONArray queryCarAccidentPayInfo(JSONObject param) {

        String sql = "SELECT C.ID,to_char(C.CREATE_DATE,'yyyy-MM-dd')CREATE_DATE,D.AMOUNT,E.NAME PAYMENT_METHOD_NAME\n" +
                "FROM SER_CAR_ACCIDENT A ,SER_LOSS_QUOTATION B,RETAIL_ORDER C,RETAIL_ORDER_PAYMENT_METHOD D,MAS_PAYMENT_METHOD E\n" +
                "WHERE A.ID = B.CAR_ACCIDENT\n" +
                "AND B.ID = C.SOURCE\n" +
                "AND D.RETAIL_ORDER_ID = C.ID\n" +
                "AND E.ID = D.PAYMENT_METHOD_TYPE " +
                "AND A.ID = :key";

        return toJSONArray(find(sql, param));

    }


    private void SQLCondition(StringBuffer sb,JSONObject param){
        if (param.containsKey("id"))
            sb.append(" AND A.ID = :id ");
        if (param.containsKey("ids"))
            sb.append(" AND A.ID in :ids ");
        if (param.containsKey("carLicense"))
            sb.append(" AND A.ID in (SELECT D.ID FROM retail_order D,fn_customer_expand E WHERE D.customer_expand_id = E.ID AND E.car_license = :carLicense) ");
        if (param.containsKey("dateApproveBeginDate"))
            sb.append(" AND TO_CHAR(A.DATE_APPROVE,'yyyy-MM-dd HH:mm:ss') > :dateApproveBeginDate ");
        if (param.containsKey("dateApproveEndDate"))
            sb.append(" AND TO_CHAR(A.DATE_APPROVE,'yyyy-MM-dd HH:mm:ss') < :dateApproveEndDate ");
        if (param.containsKey("deptId"))
            sb.append(" AND A.DEPT_ID = :deptId ");
    }

    public JSONArray findAllNotUploadData(JSONObject param){
        String sql = "select A.ID\n" +
                " from RETAIL_ORDER A\n" +
                " where DEPT_ID= :DEPT_ID\n" +
                " AND NOT EXISTS(SELECT 1 FROM FN_VEHICLE_FILE_UPLOAD_INFO S WHERE S.ORDER_ID = A.ID)" +
                " and a.create_date >:startDate" +
                " and a.create_date <=:endDate"
                ;

        return toJSONArray(find(sql, param));
    }
}
